package Orm.dbutils;

import Orm.jdbc.connectorPool.JDBCUtils;
import org.apache.commons.dbutils.DbUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 通过DbUtils轻松管理连接，事务提交，回滚等操作
 */
public class DbUtilsStudy {

    public static void main(String[] args) throws SQLException {
        loadDriverStudy();
        closeConnectionStudy();
        executeSqlStudy();
    }

    /**
     * 加载驱动学习
     */
    public static void loadDriverStudy(){
        DbUtils.loadDriver("com.mysql.cj.jdbc.Driver");
    }

    /**
     * 关闭连接
     * @throws SQLException
     */
    public static void closeConnectionStudy() throws SQLException {
        DbUtils.loadDriver("com.mysql.cj.jdbc.Driver");
        Connection connection = JDBCUtils.getConnection();
        DbUtils.close(connection);
        //提交并关闭
        DbUtils.commitAndCloseQuietly(connection);
        DbUtils.commitAndClose(connection);
    }

    /**
     * 执行sql并处理结果集并关闭连接
     * @throws SQLException
     */
    public static void executeSqlStudy() throws SQLException {
        DbUtils.loadDriver("com.mysql.cj.jdbc.Driver");
        Connection connection = JDBCUtils.getConnection();
        assert connection != null;
        Statement statement = connection.createStatement();
        String sql = "select * from city limit 3";
        ResultSet rs = statement.executeQuery(sql);
        while(rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String countryCode = rs.getString("countryCode");
            System.out.println(id +" | "+name+" | "+countryCode);
        }
        //关闭连接
        DbUtils.closeQuietly(connection,statement,rs);
    }
}
